package io.github.cameradddd.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import io.github.cameradddd.entity.AccountRecord;

/**
 * 这是一个与数据库进行直接交互的类,重写自AccountDAO
 */
public class RecordDAO {
    private static String url = "jdbc:mysql://localhost:3306/cameradd";
    private static String user = "root";
    private static String password = "Dn200405!";

    /**
     * 这是获取Connection对象的一个工厂方法
     * 
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 这是一个关闭jdbc资源的方法
     * 
     * @param conn
     * @param stmt
     * @param rs
     */
    @SuppressWarnings("unused")
    private static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }

    /**
     * 这是用于保存行的类
     * 
     * @param type
     * @param amount
     * @param remark
     * @return
     */
    // TODO save的参数变了,要去controller层修改一下
    public static void createRecord(String type, double amount, String remark) {
        if (!(type.equals("收入") || type.equals("支出"))) {
            System.err.println("type参数的内容不是收入或支出");
        }
        String sql = "INSERT INTO records(type,amount,remark) VALUES(?,?,?)";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);) {
            stmt.setString(1, type);
            stmt.setDouble(2, amount);
            stmt.setString(3, remark);
            int rowsAffected = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<AccountRecord> findAll() {
        String sql = "SELECT * FROM records";
        List<AccountRecord> recordList = new ArrayList<>();
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {
            // 遍历结果
            while (rs.next()) {
                recordList.add(new AccountRecord(
                        rs.getInt("id"),
                        rs.getTimestamp("createdDate").toLocalDateTime(),
                        rs.getString("type").equals("收入"),
                        rs.getDouble("amount"),
                        rs.getString("remark")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return recordList;
    }

    public static int getRecordNumber() {
        String sql = "SELECT COUNT(*) AS num FROM records";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {
            rs.next();
            return (int) rs.getLong("num");
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    public static double calculateAmount() {
        String sql = "SELECT SUM(CASE WHEN type = '收入' THEN amount WHEN type ='支出' THEN -amount ELSE 0 END) AS balance FROM records;";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {
            rs.next();
            return rs.getDouble("balance");
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    /**
     * 如果表为空,则不会处理
     * 
     * @return
     */
    public static void deleteRecord() {
        String sql = "DELETE FROM records WHERE id = (SELECT MAX(id) FROM records);";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void deleteRecord(int id) {
        String sql = "DELETE FROM records WHERE id = ?;";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void deleteRecord(List<Integer> idList) {
        String sql = "DELETE FROM records WHERE id = ?;";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (Integer id : idList) {
                stmt.setInt(1, id);
                stmt.executeUpdate();

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void editRecord(int id, double newAmount, String newRemark) {
        String sql = "UPDATE records SET amount = ?,remark = ? WHERE id = ?;";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setDouble(1, newAmount);
            stmt.setString(2, newRemark);
            stmt.setInt(3, id);
            stmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void editRecord(int id, double newAmount) {
        String sql = "UPDATE records SET amount = ? WHERE id = ?;";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setDouble(1, newAmount);
            stmt.setInt(2, id);
            stmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void editRecord(int id, String newRemark) {
        String sql = "UPDATE records SET amount = ? WHERE id = ?;";
        try (Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, newRemark);
            stmt.setInt(2, id);
            stmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
